package com.gitee.qdbp.jdbc.plugins.impl;

import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import com.gitee.qdbp.able.jdbc.model.LikeValue;
import com.gitee.qdbp.able.jdbc.paging.Paging;
import com.gitee.qdbp.jdbc.model.DbType;
import com.gitee.qdbp.jdbc.model.DbVersion;
import com.gitee.qdbp.jdbc.model.MainDbType;
import com.gitee.qdbp.jdbc.plugins.DbPluginHelper;
import com.gitee.qdbp.jdbc.plugins.SqlDialect;
import com.gitee.qdbp.jdbc.sql.SqlBuffer;
import com.gitee.qdbp.jdbc.sql.SqlBuilder;
import com.gitee.qdbp.jdbc.utils.DbTypes;
import com.gitee.qdbp.tools.utils.DateTools;
import com.gitee.qdbp.tools.utils.VerifyTools;

/**
 * 数据库方言处理类
 *
 * @author zhaohuihua
 * @version 190601
 */
public class SimpleSqlDialect implements SqlDialect {

    private final DbVersion dbVersion;
    private final DbPluginHelper plugins;

    public SimpleSqlDialect(DbVersion dbVersion, DbPluginHelper plugins) {
        VerifyTools.requireNonNull(dbVersion, "DbVersion");
        this.dbVersion = dbVersion;
        this.plugins = plugins;
    }

    /** 获取数据库版本信息 **/
    @Override
    public DbVersion dbVersion() {
        return dbVersion;
    }

    @Override
    public int getInItemLimit() {
        return DbTypes.equals(dbVersion.getDbType(), MainDbType.Oracle) ? 1000 : 0;
    }

    @Override
    public String rawCurrentTimestamp() {
        DbType dbType = dbVersion.getDbType();
        if (DbTypes.exists(dbType, "Oracle,MySQL,MariaDB,DB2,PostgreSQL")) {
            // 这些数据库都是支持CURRENT_TIMESTAMP的
            return "CURRENT_TIMESTAMP";
        } else if (DbTypes.equals(dbType, MainDbType.SqlServer)) {
            return "GETDATE()";
        } else { // 其他的不知道, 暂时返回CURRENT_TIMESTAMP
            return "CURRENT_TIMESTAMP";
        }
    }

    // 参考了org.hibernate.dialect.pagination.LimitHelper类及LimitHandler的子类

    /** {@inheritDoc} **/
    @Override
    public SqlBuffer buildPagingSql(SqlBuffer buffer, Paging paging) {
        SqlBuffer copied = buffer.copy();
        processPagingSql(copied, paging);
        return copied;
    }

    /** {@inheritDoc} **/
    @Override
    public void processPagingSql(SqlBuffer buffer, Paging paging) {
        if (paging == null || !paging.isPaging()) {
            return;
        }
        DbType dbType = dbVersion.getDbType();
        if (DbTypes.equals(dbType, MainDbType.Oracle)) {
            processPagingForOracle(buffer, paging);
        } else if (DbTypes.exists(dbType, MainDbType.MySQL, MainDbType.MariaDB)) {
            processPagingForMySql(buffer, paging);
        } else if (DbTypes.equals(dbType, MainDbType.DB2)) {
            processPagingForDb2(buffer, paging);
        } else if (DbTypes.equals(dbType, MainDbType.H2)) {
            processPagingForH2(buffer, paging);
        } else if (DbTypes.equals(dbType, MainDbType.PostgreSQL)) {
            processPagingForPostgreSql(buffer, paging);
        } else if (DbTypes.equals(dbType, MainDbType.SQLite)) {
            processPagingForSqlite(buffer, paging);
        } else {
            // throw new UnsupportedOperationException("Unsupported db type: " + dbType);
            processPagingForOtherwise(buffer, paging);
        }
    }

    /** 如遇到不支持的数据库, 可覆盖此方法 ( 通过DbPluginContainer.setSqlDialectCreator()注册至插件中心 ) **/
    protected void processPagingForOtherwise(SqlBuffer buffer, Paging paging) {
        processPagingForLimitOffset(buffer, paging);
    }

    protected void processPagingForMySql(SqlBuffer buffer, Paging paging) {
        SqlBuilder sql = buffer.shortcut();
        if (paging.getStart() <= 0) {
            // limit {rows}
            sql.newline().ad("LIMIT").var(paging.getRows());
        } else {
            // limit {start}, {rows}
            sql.newline().ad("LIMIT").var(paging.getStart()).ad(',').var(paging.getRows());
        }
    }

    protected void processPagingForH2(SqlBuffer buffer, Paging paging) {
        // 逻辑参考自: org.hibernate.dialect.H2Dialect
        processPagingForLimitOffset(buffer, paging);
    }

    protected void processPagingForPostgreSql(SqlBuffer buffer, Paging paging) {
        // 逻辑参考自: org.hibernate.dialect.PostgreSQLDialect
        processPagingForLimitOffset(buffer, paging);
    }

    protected void processPagingForSqlite(SqlBuffer buffer, Paging paging) {
        processPagingForLimitOffset(buffer, paging);
    }

    protected void processPagingForLimitOffset(SqlBuffer buffer, Paging paging) {
        SqlBuilder sql = buffer.shortcut();
        if (paging.getStart() <= 0) {
            // limit {rows}
            sql.newline().ad("LIMIT").var(paging.getRows());
        } else {
            // limit {rows} offset {start}
            sql.newline().ad("LIMIT").var(paging.getRows()).ad("OFFSET").var(paging.getStart());
        }
    }

    protected void processPagingForOracle(SqlBuffer buffer, Paging paging) {
        // 逻辑参考自: org.hibernate.dialect.OracleDialect
        if (paging.getStart() <= 0) {
            buffer.tabAll(1, true);
            // SELECT T_T.* FROM (
            //     {sql}
            // ) T_T WHERE ROWNUM <= {end}
            buffer.prepend("SELECT T_T.* FROM (\n");
            buffer.append("\n) T_T\nWHERE ROWNUM <= ").addVariable(paging.getEnd());
        } else {
            buffer.tabAll(2, true);
            // SELECT * FROM (
            //     SELECT T_T.*, ROWNUM R_N FROM (
            //         {sql}
            //     ) T_T WHERE ROWNUM <= {end}
            // ) WHERE R_N > {start}
            buffer.prepend("SELECT * FROM (\n\tSELECT T_T.*, ROWNUM R_N FROM (\n");
            buffer.append("\n\t) T_T WHERE ROWNUM <= ").addVariable(paging.getEnd());
            buffer.append("\n) WHERE R_N > ").addVariable(paging.getStart());
        }
    }

    // DB2的分页参数不支持占位符
    protected void processPagingForDb2(SqlBuffer buffer, Paging paging) {
        // 逻辑参考自: org.hibernate.dialect.DB2Dialect
        if (paging.getStart() <= 0) {
            // FETCH FIRST {end} ROWS ONLY
            String end = String.valueOf(paging.getEnd());
            buffer.append('\n').append("FETCH FIRST").append(' ', end, ' ').append("ROWS ONLY");
        } else {
            buffer.tabAll(2, true);
            // SELECT * FROM (
            //     SELECT T_T.*, ROWNUMBER() OVER(ORDER BY ORDER OF T_T) AS R_N 
            //     FROM (
            //         {sql}
            //         FETCH FIRST {end} ROWS ONLY
            //     ) AS T_T
            // ) WHERE R_N > {start} ORDER BY R_N
            String start = String.valueOf(paging.getStart());
            String end = String.valueOf(paging.getEnd());
            buffer.prepend("SELECT * FROM (\n\tSELECT T_T.*, ROWNUMBER() OVER(ORDER BY ORDER OF T_T) AS R_N FROM (\n");
            buffer.append('\n', '\t', '\t').append("FETCH FIRST").append(' ', end, ' ').append("ROWS ONLY");
            buffer.append('\n', '\t').append(") AS T_T\n)");
            buffer.append(' ').append("WHERE").append(' ').append("R_N > ").append(start);
            buffer.append(' ').append("ORDER BY").append(' ').append("R_N");
        }
    }

    /** {@inheritDoc} **/
    @Override
    public String toPinyinOrderByExpression(String columnName) {
        DbType dbType = dbVersion.getDbType();
        if (DbTypes.equals(dbType, MainDbType.Oracle)) {
            return columnName; // 系统默认排序方式就是拼音: "NLSSORT(" + columnName + ",'NLS_SORT=SCHINESE_PINYIN_M')";
        } else if (DbTypes.exists(dbType, MainDbType.MySQL, MainDbType.MariaDB)) {
            return "CONVERT(" + columnName + " USING GBK)";
        } else {
            return columnName;
        }
    }

    /** {@inheritDoc} **/
    @Override
    public String variableToString(Boolean variable) {
        return String.valueOf(Boolean.TRUE.equals(variable) ? 1 : 0);
    }

    /** {@inheritDoc} **/
    @Override
    public String variableToString(String variable) {
        return new StringBuilder().append("'").append(variable.replace("'", "''")).append("'").toString();
    }

    /** {@inheritDoc} **/
    @Override
    public String variableToString(Date date) {
        StringBuilder sb = new StringBuilder();
        DbType dbType = dbVersion.getDbType();
        if (DbTypes.exists(dbType, MainDbType.Oracle, MainDbType.DB2)) {
            sb.append("TO_TIMESTAMP").append('(');
            sb.append("'").append(DateTools.toNormativeString(date)).append("'");
            sb.append(',');
            sb.append("'YYYY-MM-DD HH24:MI:SS.FF'");
            sb.append(')');
            return sb.toString();
        } else if (DbTypes.equals(dbType, MainDbType.PostgreSQL)) {
            sb.append("TO_TIMESTAMP").append('(');
            sb.append("'").append(DateTools.toNormativeString(date)).append("'");
            sb.append(',');
            sb.append("'YYYY-MM-DD HH24:MI:SS.MS'");
            sb.append(')');
            return sb.toString();
        } else if (DbTypes.equals(dbType, MainDbType.H2)) {
            sb.append("PARSEDATETIME").append('(');
            sb.append("'").append(DateTools.toNormativeString(date)).append("'");
            sb.append(',');
            sb.append("'yyyy-MM-dd HH:mm:ss.SSS'");
            sb.append(')');
            return sb.toString();
        } else {
            sb.append("'").append(DateTools.toNormativeString(date)).append("'");
            return sb.toString();
        }
    }

    /** {@inheritDoc} **/
    @Override
    public SqlBuffer buildLikeSql(LikeValue fieldValue) {
        SqlBuilder buffer = new SqlBuilder(this, plugins);
        buffer.ad("LIKE").var(fieldValue.getPatternString());
        if (fieldValue.getEscapeChar() != 0) {
            buffer.ad("ESCAPE").var(fieldValue.getEscapeChar());
        }
        return buffer.out();
    }

    /** {@inheritDoc} **/
    @Override
    public SqlBuffer buildLikeSql(String fieldValue) {
        SqlBuilder buffer = new SqlBuilder(this, plugins);
        buffer.ad("LIKE");
        LikeValue likeValue = parseLikeString(fieldValue);
        Object pattern = likeValue == null ? fieldValue : likeValue.getPatternString();
        DbType dbType = dbVersion.getDbType();
        if (DbTypes.exists(dbType, MainDbType.Oracle, MainDbType.DB2, MainDbType.PostgreSQL)) {
            buffer.ad("('%'||").var(pattern).ad("||'%')");
        } else if (DbTypes.exists(dbType, MainDbType.MySQL, MainDbType.MariaDB, MainDbType.H2)) {
            buffer.ad("CONCAT('%',").var(pattern).ad(",'%')");
        } else if (DbTypes.equals(dbType, MainDbType.SqlServer)) {
            buffer.ad("('%'+").var(pattern).ad("+'%')");
        } else {
            buffer.ad("('%'||").var(pattern).ad("||'%')");
        }
        if (likeValue != null && likeValue.getEscapeChar() != 0) {
            buffer.ad("ESCAPE").var(likeValue.getEscapeChar());
        }
        return buffer.out();
    }

    /** {@inheritDoc} **/
    @Override
    public SqlBuffer buildStartsWithSql(String fieldValue) {
        SqlBuilder buffer = new SqlBuilder(this, plugins);
        buffer.ad("LIKE");
        LikeValue likeValue = parseLikeString(fieldValue);
        Object pattern = likeValue == null ? fieldValue : likeValue.getPatternString();
        DbType dbType = dbVersion.getDbType();
        if (DbTypes.exists(dbType, MainDbType.Oracle, MainDbType.DB2, MainDbType.PostgreSQL)) {
            buffer.ad('(').var(pattern).ad("||'%')");
        } else if (DbTypes.exists(dbType, MainDbType.MySQL, MainDbType.MariaDB, MainDbType.H2)) {
            buffer.ad("CONCAT(").var(pattern).ad(",'%')");
        } else if (DbTypes.equals(dbType, MainDbType.SqlServer)) {
            buffer.ad('(').var(pattern).ad("+'%')");
        } else {
            buffer.ad('(').var(pattern).ad("||'%')");
        }
        if (likeValue != null && likeValue.getEscapeChar() != 0) {
            buffer.ad("ESCAPE").var(likeValue.getEscapeChar());
        }
        return buffer.out();
    }

    /** {@inheritDoc} **/
    @Override
    public SqlBuffer buildEndsWithSql(String fieldValue) {
        SqlBuilder buffer = new SqlBuilder(this, plugins);
        buffer.ad("LIKE");
        LikeValue likeValue = parseLikeString(fieldValue);
        Object pattern = likeValue == null ? fieldValue : likeValue.getPatternString();
        DbType dbType = dbVersion.getDbType();
        if (DbTypes.exists(dbType, MainDbType.Oracle, MainDbType.DB2, MainDbType.PostgreSQL)) {
            buffer.ad("('%'||").var(pattern).ad(')');
        } else if (DbTypes.exists(dbType, MainDbType.MySQL, MainDbType.MariaDB, MainDbType.H2)) {
            buffer.ad("CONCAT('%',").var(pattern).ad(')');
        } else if (DbTypes.equals(dbType, MainDbType.SqlServer)) {
            buffer.ad("('%'+").var(pattern).ad(')');
        } else {
            buffer.ad("('%'||").var(pattern).ad(')');
        }
        if (likeValue != null && likeValue.getEscapeChar() != 0) {
            buffer.ad("ESCAPE").var(likeValue.getEscapeChar());
        }
        return buffer.out();
    }

    private static final char[] ESCAPE_CHARS = "#!:$^-+".toCharArray();

    // 如果不存在特殊字符, 返回null, 否则返回LikeValue对象
    protected LikeValue parseLikeString(String likeString) {
        // 判断字符串中有没有%和_这类特殊字符
        char[] likeChars = likeString.toCharArray();
        boolean existPattern = false;
        Map<Character, ?> charMaps = new HashMap<>();
        for (char c : likeChars) {
            charMaps.put(c, null);
            if (c == '%' || c == '_') {
                existPattern = true;
            }
        }
        // 不存在特殊字符
        if (!existPattern) {
            return null;
        }
        // 从ESCAPE_CHARS中选择一个字符作为EscapeChar
        char escapeChar = 0;
        for (char c : ESCAPE_CHARS) {
            if (!charMaps.containsKey(c)) {
                escapeChar = c;
                break;
            }
        }
        if (escapeChar == 0) {
            escapeChar = '#';
        }
        // 如果字符是%或_或escapeChar, 则在前面加上escapeChar
        StringBuilder buffer = new StringBuilder();
        for (char c : likeChars) {
            if (c == '%' || c == '_' || c == escapeChar) {
                buffer.append(escapeChar);
            }
            buffer.append(c);
        }
        return new LikeValue(escapeChar, buffer.toString());
    }

    /**
     * SqlDialect创建接口的SimpleSqlDialect实现类
     *
     * @author zhaohuihua
     * @version 20201018
     */
    public static class Creator extends CacheCreator implements DbPluginHelper.Aware {

        /** 插件容器 **/
        private DbPluginHelper plugins;

        /** 插件容器 **/
        @Override
        public void setPlugins(DbPluginHelper plugins) {
            this.plugins = plugins;
        }

        @Override
        protected SqlDialect newSqlDialect(DbVersion version) {
            return new SimpleSqlDialect(version, plugins);
        }
    }
}
